<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
        <meta name="language" content="sk" /> 
        
<style type="text/css">
#report
{
font-family:font-family: Verdana, Arial, Helvetica, sans-serif;
width:100%;
border-collapse:collapse;
}
td, th 
{
font-size:1em;
border:1px solid #98bf21;
padding:3px 7px 2px 7px;
}
th 
{
font-size:16px;
text-align:left;
padding-top:5px;
padding-bottom:4px;
background-color:#A7C942;
color:#ffffff;
}
tr.alt td 
{
color:#000000;
background-color:#EAF2D3;
}
</style>

</head>
<body>
<h2>Report #3: Výpožičky študentov s vysokým priemerom</h2>
<table  id="report">

<tr>
<th><b>AIS ID</b></th>
<th><b>Meno</b></th>
<th><b>Priezvisko</b></th>
<th><b>Študijný program</b></th>
<th><b>Ročník</b></th>
<th><b>Študijný priemer</b></th>
<th><b>Názov vypožičanej publikácie</b></th>
<th><b>Autor publikácie</b></th>
<th><b>Začiatok výpožičky</b></th>
<th><b>Koniec výpožičky</b></th>
</tr>


<?php


	function getConnection() {
		$con = mysql_pconnect('147.175.187.53', 'root', 'qwerty123', 65536);
		if (!$con) {
			echo('<h3 style="color: Red;">Cannot connect to MySQL server &nbsp;&nbsp; :\'(</h3>');
			die('Could not connect: ' . mysql_error());
		}
		mysql_query("SET NAMES 'utf8'");
		mysql_select_db("dwh", $con);
		return $con;
	}
	
	$connection = getConnection();	
	$queryString = "SELECT TermKey FROM `dwh`.`term` WHERE StartDate='".$_GET['StartDate']."'" ;
	$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
	$StartDate=0;
	while($row = mysql_fetch_array($queryAnswer) ) {
		
		$StartDate=$row["TermKey"];
	}
	
	
	$queryString = "SELECT TermKey FROM `dwh`.`term` WHERE EndDate='".$_GET['EndDate']."'" ;
	$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
	$EndDate=0;
	while($row = mysql_fetch_array($queryAnswer) ) {
		
		$EndDate=$row["TermKey"];
	}
	
	$queryString = "SELECT s.AIS_ID, s.FirstName, s.LastName,fs.Name, s.Grade,ar.SummaryRating, b.Title, b.Authors, bf.StartTimeKey, 
					bf.EndTimeKey FROM `dwh`.`aggregateratings` AS ar 
					left join `dwh`.`student` s on s.StudentKey=ar.StudentKey
					left join `dwh`.`libraryaccount` la on la.StudentKey=s.StudentKey
					left join `dwh`.`borrowingfacts` bf on bf.LibraryAccountKey=la.LibraryAccountKey
					left join `dwh`.`book` b on b.BookKey=bf.BookKey
					left join `dwh`.`fieldofstudy` fs on fs.FieldOfStudyKey=s.FieldOfStudyKey
					WHERE StartTermKey=".$StartDate." AND ENDTermKey=".$EndDate." ORDER BY SummaryRating;" ;
	$queryAnswer = mysql_query($queryString,$connection)or die(mysql_error()); 
	
	while($row = mysql_fetch_array($queryAnswer) ) {
		$summaryRating=$row["SummaryRating"];
		if ($_GET['SummaryRating']>$summaryRating){
			//list($start2,$DayTime)= split("-00-00",$row["StartTimeKey"]);
			//list($end2,$DayTime)= split("-00-00",$row["EndTimeKey"]);
			$gStart2 = $_GET['StartDate'];
			$gEnd2 = $_GET['EndDate'];
			$start2=$row["StartTimeKey"];
			$end2=$row["EndTimeKey"];
			
			$start = strtotime($start2);
			$end=strtotime($end2);
			$gStart = strtotime($gStart2);
			$gEnd = strtotime($gEnd2);
			//echo "gStart2".$gStart2;
			//echo "<br/>";
			//echo "gEnd2".$gEnd2;
			//echo "<br/>";
			//echo "Start2".$start2;
			//echo "<br/>";
			//echo "End2".$end2;
			//echo "<br/>";
			if (strcmp($start2,$gStart2)>0 && strcmp($end2,$gEnd2)<0 ) {
    			
				
    			// break;
			
				echo "<tr>";
				echo "<td>".$row["AIS_ID"]."</td>";
				echo "<td>".$row["FirstName"]."</td>";
				echo "<td>".$row["LastName"]."</td>";
				echo "<td>".$row["Name"]."</td>";
				echo "<td>".$row["Grade"]."</td>";
				echo "<td>".$row["SummaryRating"]."</td>";
				echo "<td>".$row["Title"]."</td>";
				echo "<td>".$row["Authors"]."</td>";
				echo "<td>".$row["StartTimeKey"]."</td>";
				echo "<td>".$row["EndTimeKey"]."</td>";
				echo "</tr>";
			}
			if ($row["Title"]==""){
			echo "<tr>";
				echo "<td>".$row["AIS_ID"]."</td>";
				echo "<td>".$row["FirstName"]."</td>";
				echo "<td>".$row["LastName"]."</td>";
				echo "<td>".$row["Name"]."</td>";
				echo "<td>".$row["Grade"]."</td>";
				echo "<td>".$row["SummaryRating"]."</td>";
				echo "<td>".$row["Title"]."</td>";
				echo "<td>".$row["Authors"]."</td>";
				echo "<td>".$row["StartTimeKey"]."</td>";
				echo "<td>".$row["EndTimeKey"]."</td>";
				echo "</tr>";
			}
		}
	}
	
	mysql_close($connection);
?>

</table>

</body>
</html>